# -*- coding : utf-8 -*-

import sys
sys.path.append('../')

import re
import os
import uuid
import tqdm
import json
import pymysql
import requests
import threadpool
import traceback
import logging
import psycopg2

import tornado.ioloop
import tornado.options
import tornado.web
import tornado.httpserver
import tornado.httpclient
from tornado.options import define
from tornado.options import options

# from data_generate import utils
# from internal_server.bot.financial_sales.agent import user_questioning, user_situation, choose_sales_skill
# from internal_server.bot.financial_sales.agent import conversation_agent
from openpyxl import Workbook, load_workbook
from datetime import datetime
pool = threadpool.ThreadPool(30)
logging.basicConfig(format='%(asctime)s - %(pathname)s[line:%(lineno)d] - %(levelname)s: %(message)s',
                    level=logging.INFO)
pattern = re.compile(r'[\000-\010]|[\013-\014]|[\016-\037]')

attitude_dict = {'肯定': '肯定态度', '否定': '否定态度', '无明确回应': '不明态度',
                 '1': '肯定态度', '-1': '否定态度', '0': '不明态度'}

intent_341_name = {
    '是否进口药都可以报销': 0, '是否收费': 0, '没钱': -1, '确认平台': 0,
    '操作问题-操作麻烦': -1, '二次不需要': -1, '有没有风险': 0, '没有网络': -1,
    '非本人操作': -1, '理赔问题': 0, '售后问题': 0, '什么软件': 0,
    '以前有疾病现在好了，能买吗': 0, '是否属于商业保险': 0,
    '赔过以后还能续保吗': 0, '挂断后操作': 0, '同业': 0, '报销比例': 0,
    '你是在卖保险吗': 0, '需要连续续保': 0, '有人联系过我': 0, '短信点不开': -1,
    '三次在忙': 0, '投保有什么好处': 0, '侵犯我隐私了': -1, '在操作': 1,
    '哪家公司承保': 0, '询问工号': 0, '操作问题-操作目的': 0, '等待期': 0,
    '是否全国内都保': 0, '重大疾病都有哪些': 0, '停一下，听我说': 0, '在忙-可协商': 0,
    '都有什么保障': 0, '什么是百万医疗、自费药': 0, '对保险有了解': 0, '医疗垫付问题': 0,
    '保险类型': 0, '发票问题': 0, '办理方式': 0, '你们怎么还卖保险': 0,
    '什么平台': 0, '理赔标准': 0, '发短信': 0, '产品属性': 0,
    '是否与现有保险冲突': 0, '不愿意点链接': -1, '保障时效': 0, '赠险相关问题': 0,
    '智能语音客服': 0, '我买不了保险': -1, '保费问题': 0, '保费缴存周期': 0,
    '确认是否承保': 0, '语音信箱': 0, '联系方式': 0, '客户会考虑': 0,
    '你怎么有我号码': 0, '不可理赔条件': 0, '要钱就不要了': -1, '是人还是机器人': 0,
    '第2个月保费': 0, '买保险的意义': 0, '什么医疗保障': 0, '是否返本': 0,
    '在忙-预约回访': 0, '非本人': -1, '加微信': 0, '公众号问题': 0,
    '重说': 0, '保障问题': 0, '我没有买过': -1, '操作问题-不会操作': -1,
    '生效日期': 0, '年龄不符': -1, '我有医社保了': 0, '什么产品': 0,
    '投诉': -1, '来源点错了': -1, '投保条件': 0, '理赔流程': 0,
    '没听懂': 0, '有需要再联系': 0, '非本人接听': -1, '续保费用': 0,
    '退款问题': 0, '是否可以为家人投保': 0, '不需要-暂时/先': -1, '公司在哪': 0,
    '已经取消了': -1, '不需要-无原因': -1, '你是谁': 0, '已有其他商业保险': 0,
    '保障时效到期': 0, '什么时间联系': 0, '可理赔条件': 0, '这是一个什么样的保险': 0,
    '保障额度': 0, '关于免赔额': 0, '报销信息': 0, '怀疑号码': -1,
    '强烈拒绝': -1, '在忙-快点说': 0, '非本人操作轮询': -1, '已经买过该产品': 0,
    '没收到短信': -1, '医保问题': 0, '关于活动': 0, '三次不需要': -1,
    '缴费问题': 0, '办理是否要体检': 0, '在忙-必须挂机': 0, '挂断后操作轮询': 0,
    '我去其他渠道买': 0, '客户要求转人工': 0, '暂时不想操作': -1, '哪些医院可以报销': 0,
    '住院都能报吗': 0, '断缴问题': 0, '打电话的目的': 0, '询问绿通': 0,
    '担心自己被扣费': 0, '如何查询保单': 0, '无意向调侃': 0, '续期交过了': 0,
    '疑难问题': 0, '反驳营销介绍': 0, '拒绝福利': -1, '已经关注公众号': 0,
    '希望从头引导操作': 0, '关于电话医生': 0, '关于体检卡': 0, '不用微信': -1,
    '没找到黄色服务通知': 0, '什么专享特权': 0, '什么是活跃度分数': 0
}
intent_222_name = {
    '关于产品': 0, '你怎么有我号码': 0, '审核不过-黑户': -1, '没听懂': 0,
    '已操作': 0, '系统问题-证件没带': 0, '什么时间回访': 0, '投诉': -1,
    '多次表示-客户会操作': 0, '不需要-会考虑': -1, '审核条件': 0, '系统问题-APP问题': 0,
    '特殊情况': 0, '链接短信': 0, '利息高': 0, '询问工号': 0,
    '申请渠道问题': 0, '怎么注销': 0, '还款方式': 0, '临时额度还是永久额度': 0,
    '客户会操作': 0, '身份证/银行卡没带': 0, '已激活': 0, '打电话有什么目的': 0,
    '生病': 0, '审核不过-担心通过不了': -1, '审核不过-无原因': -1, '操作问题-操作麻烦': 0, '降额问题': 0,
    '如何提升额度': 0, '下款时间': 0, '什么平台': 0, '要求挂机': 0,
    '关于活动': 0, '打的客户什么号': 0, '是否已经激活额度': 0, '语音信箱': 0,
    '不需要-不缺钱': -1, '非本人操作': -1, '怀疑平台': -1, '有人联系过': 0,
    '会考虑': 0, '优惠券使用方法': 0, '额度多少': 0, '公司在哪': 0,
    '分期期数': 0, '咨询京东其他产品': 0, '查询征信': 0, '转人工': 0, '拨打时间': 0, '有人打电话让注销金条账户': 0,
    '操作问题-不会操作': -1, '别给我打电话了': -1, '打断倾听': 0, '是否用过什么产品': 0,
    '在忙-无原因': 0, '客户咨询是否能借款': 0, '不需要-无原因': -1, '你找谁': 0,
    '是否能通过/使用': 0,
    '审核不过-年龄问题': -1, '已借款': 0, '加微信': 0, '年费问题': 0,
    '不会操作': -1, '有需要再联系': 0, '个人费用': 0, '敏感客群': 0,
    '活动期限': 0, '产品是否合规': 0, '怎么领取优惠券': 0, '激活是否必须使用': 0,
    '额度用完': 0, '产品费用': 0,  '不需要-使用其他平台': -1, '系统问题-app问题': 0,
    '资金用途': 0, '客户会考虑': 0, '提前还款': 0, '已经注销': -1,
    '怎么联系客服': 0, '系统问题-证件问题': 0, '还不起': 0, '人还是机器人': 0,
    '是否免费借款': 0, '无意向调侃用户': 0, '强烈拒绝': -1, '额度问题-额度低': 0,
    '已卸载APP': -1, '确认平台': 0, '在忙-主动邀约': 0, '在忙-有原因': 0,
    '在忙-快点说': 0, '没点过': -1, '品牌反应': 0, '我没有注册过': -1,
    '要求反馈立刻处理': 0, '没有企业': -1, '不想加陌生人': -1, '安全问题': 0,
    '激活费用': 0, '是否有销售任务': 0, '发票问题': 0, '怎么兑换': 0,
    '怀疑信息泄露': -1, '办过会员/是VIP': 0, '忘记了': -1, '还款提醒': 0,
    '担心给联系人打电话': -1, '快递问题': 0, '担心通过不了': -1, '在操作': 1,
    '担保费': 0, '生意或消费': 0, '让本人接电话': 0, '没有资金需求': -1,
}
intent_name = {
    '222': intent_222_name,
    '341': intent_341_name
}
company_id_dict = {'2186': '222', '2199': '341', '2201': '341'}

def mysql_connection(min_date, max_date, company_id, dm_version, records, data_type,
                     dialogue_round, robot_answer_duration, human_answer_duration, deal):
    connection = pymysql.connect(
        host="am-8vbwn20384jdq3vq185480.zhangbei.ads.aliyuncs.com",  # 内网
        # host="39.103.215.119",  # 公网
        port=3306,
        user="ds_user",
        passwd="Moxi123#",
        charset='utf8mb4',
        cursorclass=pymysql.cursors.DictCursor
    )
    cursor = connection.cursor()

    sql_dialogue = '''
    select customer_id, dm_session_id, full_name, sex, transfer_success_time 
    from ods_outbound_data_platform.outbound_call_result  
    where call_status in ("normalConnection", "transferFail") 
    '''
    if company_id:
        sql_dialogue += 'and company_id = {} '.format(company_id)
    if dm_version:
        sql_dialogue += ' and dm_version = "{}" '.format(dm_version)
    if data_type == 'onlyHuman':
        sql_dialogue += ' and transfer_success_time is not NULL ' \
               ' and human_answer_duration >= {} '.format(human_answer_duration)

        if company_id == '2199':
            sql_dialogue += 'and trim(dm_version) in ("赠转魔方新信收1call-1102", "赠转魔方新信收1callFAB测试-0106", ' \
                            '"赠转魔方新信收1callFAB测试-0214", "星火保均分1callFAB-0317", "风险测评B版-0208", "风险测评C版-0321") '
        if company_id == '2186':
            sql_dialogue += 'and dm_version like "%NBS%" '
    if data_type == 'onlyBot':
        sql_dialogue += ' and transfer_success_time is NULL '
    if robot_answer_duration:
        sql_dialogue += ' and robot_answer_duration >= {} '.format(robot_answer_duration)
    if dialogue_round:
        sql_dialogue += ' and dialogue_round >= {} '.format(dialogue_round)

    sql_dialogue += '''

    and call_start_time >= "{}" 
    and call_start_time <= "{}" 
    limit {} 
    '''.format(min_date, max_date, records)

    if deal == '1' and company_id == '2199':
        sql_deal = '''
            select customer_id, JSON_EXTRACT(json_data, '$.source') as tag 
            from ods_outbound_sale_platform.ods_breakpoint_data 
            where company_id = 2199 
            and JSON_EXTRACT(json_data, '$.source') = '订单' 
            and breakpoint_time >= "{}" 
        '''.format(min_date)

        sql = '''

        with A as (
                {}
                ),
             B as (
                {}
                ) 
        select A.customer_id, A.dm_session_id, A.full_name, A.sex, A.transfer_success_time, B.tag 
        from A left join B on A.customer_id = B.customer_id

        '''.format(sql_dialogue, sql_deal)
    elif deal == '1' and company_id == '2144':

        sql_deal = '''
        
            select customer_id, stage as tag 
            from ods_outbound_sale_platform.ods_breakpoint_data 
            where company_id = 2144 
            and stage = "复贷" 
            and breakpoint_time >= "{}" 
            group by customer_id
            
        '''.format(min_date)

        sql = '''
        
        with A as (
                {}
                ),
             B as (
                {}
                ) 
        select A.customer_id, A.dm_session_id, A.full_name, A.sex, A.transfer_success_time, B.tag 
        from B left join A on B.customer_id = A.customer_id

        '''.format(sql_dialogue, sql_deal)

    else:
        sql = sql_dialogue

    cursor.execute(sql)
    results = cursor.fetchall()

    return results


def hologres_connection(min_date, max_date, company_id, dm_version):
    connection = psycopg2.connect(
        host="hgprecn-cn-2r42t31q3003-cn-zhangjiakou.hologres.aliyuncs.com",
        port=80,
        user="LTAI5tMsnFz7WRxKJyBDK5uv",
        password="SZxfgbrWpDYaHOR4HsU5ENxDOw6Xxt",
        dbname="screen"
    )

    cursor = connection.cursor()

    sql = '''
    select ocr.id, ocr.customer_id, ocr.dm_session_id, ocr.case_id, ocr.company_id, ocr.batch_id, ocr.call_task_id, 
    TO_CHAR((answer_time :: TIMESTAMPTZ) AT TIME ZONE 'Asia/Shanghai', 'YYYY-MM-DD HH24:MI:SS') answer_time, 
    TO_CHAR((call_end_time :: TIMESTAMPTZ) AT TIME ZONE 'Asia/Shanghai', 'YYYY-MM-DD HH24:MI:SS') call_end_time, 
    ocr.answer_duration, ocr.voice_path
    from screen.public.outbound_call_result ocr 
    where ocr.call_start_time > '{}' 
    and ocr.call_start_time < '{}' 
    and ocr.answer_time is not null 
    and ocr.dm_session_id is not null 
    and ocr.answer_duration >= 0
    and ocr.company_id = {} 
    '''.format(min_date, max_date, company_id, dm_version)

    # ('金条-拉新激活纯机-希爱-1212', '金条-拉新激活纯机-希爱-0919')

    cursor.execute(sql)
    results = cursor.fetchall()

    return results


class dialogueDataHandler(tornado.web.RequestHandler):

    def get(self):
        min_date = self.get_argument('minDate')
        max_date = self.get_argument('maxDate')
        records = self.get_argument('records', "8000")
        company_id = self.get_argument('companyId', '')
        dm_version = self.get_argument('dmVersion', '')
        data_type = self.get_argument('dataType', 'ALL')
        human_pre = self.get_argument('humanPro', '')
        deal = self.get_argument('deal', '0')
        dialogue_round = self.get_argument('dialogueRound', '')
        robot_answer_duration = self.get_argument('robotAnswerDuration', '')
        human_answer_duration = self.get_argument('humanAnswerDuration', '10')

        user_key_word = self.get_argument('userKeyWord', '')
        seat_key_word = self.get_argument('seatKeyWord', '')

        if data_type in ['ALL', 'onlyHuman'] and not human_pre:
            self.write('获取人人对话需要提供前缀')
            return

        file_name = self.task(min_date, max_date,
                              company_id, dm_version, records,
                              data_type, human_pre,
                              dialogue_round, robot_answer_duration, human_answer_duration,
                              deal, user_key_word, seat_key_word)

        self.send_file(file_name)

    def send_file(self, file_name):
        # http头 浏览器自动识别为文件下载
        self.set_header('Content-Type', 'application/octet-stream')
        # 下载时显示的文件名称
        self.set_header('Content-Disposition', 'attachment; filename={}'.format(file_name))
        with open(file_name, 'rb') as f:
            while True:
                data = f.read(1024)
                if not data:
                    break
                self.write(data)
        os.remove(file_name)
        # # 记得有finish哦
        self.finish()

    def task(self, min_date, max_date, company_id, dm_version, records, data_type, human_pre,
             dialogue_round, robot_answer_duration, human_answer_duration, deal, user_key_word, seat_key_word):

        wb = Workbook()
        ws = wb.active
        ws.append([
            '客户ID', 'sessionId', '角色', '原始文本', '过滤文本', '标签'
        ])

        def multi_task(customer_id, session_id, full_name, sex, data_type, human_pre):

            if data_type in ['ALL', 'onlyHuman'] and human_pre:
                dialgue_type_list = ['', human_pre]
            else:
                dialgue_type_list = ['', ]
            url = 'http://172.26.2.56:8630/report/getDetailedRecord/?sessionId={}'
            # url = 'http://8.142.85.77:8630/report/getDetailedRecord/?sessionId={}'

            sign = True
            content_list = list()
            for dialgue_type in dialgue_type_list:
                response = requests.get(url.format(dialgue_type + session_id)).text
                res = json.loads(response)['result'] if json.loads(response)['result'] else list()
                for j in range(len(res)):
                    try:
                        speaker_type = res[j]['speakerType']
                        msg_content = res[j]['msgContent']
                        content_label = res[j]['contentLabel'] if res[j]['contentLabel'] else list()
                        content_label = self.process_label(content_label)
                        # msg_content_processed = self.process_msg(msg_content, speaker_type, content_label)
                        msg_content_processed, content_label = self.process_msg(
                            msg_content, speaker_type, ','.join(content_label)
                        )
                        if speaker_type == 'USER' and res[j]['idlResultJson']:
                            try:
                                standard_query = json.loads(res[j]['idlResultJson'])['standardQuery']
                                original_query = json.loads(res[j]['idlResultJson'])['original_query']
                            except Exception as err:
                                standard_query = json.loads(res[j]['idlResultJson'])['standard_query']
                                original_query = json.loads(res[j]['idlResultJson'])['original_query']
                            attitude = json.loads(res[j]['idlResultJson']).get('originalAttitude', '')

                            content_label = standard_query if \
                                standard_query and standard_query not in ['NOINTENT', '肯定态度', '否定态度', '无态度'] \
                                else ''
                        else:
                            attitude = ''
                            original_query = ''
                            content_label = self.process_label_return_single(content_label, company_id)

                        # openxyl不兼容字符去除
                        for result in pattern.finditer(msg_content):
                            msg_content = msg_content.replace(result.group(), '')
                        for result in pattern.finditer(msg_content_processed):
                            msg_content_processed = msg_content_processed.replace(result.group(), '')

                        if speaker_type == 'USER' and user_key_word and user_key_word in msg_content:
                            sign = False
                        if speaker_type != 'USER' and seat_key_word and seat_key_word in msg_content:
                            sign = False

                        content_list.append([
                            customer_id, session_id,
                            '销售员' if speaker_type in ('IVR', 'SEAT') else '用户', msg_content, msg_content_processed,
                            content_label if j else '', original_query,
                            attitude if speaker_type in ('IVR', 'SEAT') else ''

                        ])
                    except Exception as err:
                        traceback.print_exc()
                        print(customer_id, session_id, msg_content, content_label)

            if (not user_key_word and not seat_key_word) or ((user_key_word or seat_key_word) and not sign):
                for content in content_list:
                    ws.append(content)

                ws.append([])

            progress_bar.update(1)

        # 多进程数据准备
        results = mysql_connection(min_date, max_date, company_id, dm_version, records, data_type,
                                   dialogue_round, robot_answer_duration, human_answer_duration, deal)
        arg_parameter = list()
        for result in tqdm.tqdm(results):
            if deal == '1' and result['tag'] and '订单' in result['tag']:
                continue

            customer_id = result['customer_id']
            session_id = result['dm_session_id']
            full_name = result['full_name']
            if result['sex'] == 1:
                sex = '男'
            elif result['sex'] == 0:
                sex = '女'
            else:
                sex = '无'
            if session_id:
                arg_parameter.append((
                    [customer_id, session_id, full_name, sex, data_type, human_pre], None
                ))
        print(len(arg_parameter))
        progress_bar = tqdm.tqdm(total=len(arg_parameter))
        request = threadpool.makeRequests(multi_task, arg_parameter)
        [pool.putRequest(req) for req in request]
        pool.wait()
        logging.info('话术识别结束')

        wb.save('./{}_{}_{}_{}.xlsx'.format(company_id, min_date, max_date, records))

        return './{}_{}_{}_{}.xlsx'.format(company_id, min_date, max_date, records)


    def process_msg(self, msg_content, speaker_type, label):

        FAQ_name = ''
        return_content = list()
        if speaker_type == 'IVR':
            for content in msg_content.split('|')[1:]:
                if content.split('#')[0].replace(' ', '').replace('}', '').replace('@@notbreak@@', '').replace(
                        '您不要挂电话，马上为您服务', '').replace('好的，您不要挂机，马上为您服务', ''):
                    return_content.append(
                        content.split('#')[0].replace(' ', '').replace('}', '').replace('@@notbreak@@', '').replace(
                            '您不要挂电话，马上为您服务', '').replace('好的，您不要挂机，马上为您服务', ''))

            if 'FAQ:' in msg_content:
                FAQ_name = msg_content.split('FAQ:')[1].split('》')[0].split(']')[0]
        else:
            return_content.append(
                msg_content.split(']')[-1].replace('您不要挂电话马上为您服务', '').replace('哦好的您不要挂机啊马上为您服务。', ''))

        if ':' in FAQ_name:
            FAQ_name = FAQ_name.split(':')[-1]

        if '命中二次不需要' in msg_content and '用户主动表示:不需要-无原因' not in label:
            label += ',用户主动表示:不需要-无原因'
        if '命中一次同业' in msg_content and '用户主动表示:同业' not in label:
            label += ',用户主动表示:同业'
        if '二次在忙可协商' in msg_content and '用户主动表示:在忙-可协商' not in label:
            label += ',用户主动表示:在忙-可协商'
        if '二次已有保险' in msg_content and '用户主动表示:已有其他商业保险' not in label:
            label += ',用户主动表示:已有其他商业保险'
        if '四次不需要挂机' in msg_content and '用户主动表示:不需要' not in label:
            label += ',用户主动表示:不需要-无原因'
        if '命中一次强烈拒绝' in msg_content and '用户主动表示:强烈拒绝' not in label:
            label += ',用户主动表示:强烈拒绝'
        if '命中一次在忙-必须挂机' in msg_content and '用户主动表示:在忙-必须挂机' not in label:
            label += ',用户主动表示:在忙-必须挂机'
        if '肯定》' in msg_content and '用户肯定' not in label:
            label += ',坐席询问用户:某某某-用户肯定'
        if '否定》' in msg_content and '用户否定' not in label:
            label += ',坐席询问用户:某某某-用户否定'
        if '实体:明确同意' in msg_content:
            label += ',坐席询问用户:某某某-用户肯定'

        if FAQ_name and FAQ_name not in label:
            label = label + ',{}'.format(FAQ_name) if label else FAQ_name

        return '，'.join(return_content), label


    def process_label(self, content_label):

        result_label = list()
        for label in content_label:
            if label.startswith('Q') or label.startswith('数字') or label.startswith('价格') or \
            label.startswith('时间段') or label.startswith('时间点') or label.startswith('年龄实体') or \
            label.startswith('发短信') or label in ['转人工', '挂机', '加黑名单'] or \
            '用户分类' in label or label.endswith(':') or label.endswith('命中FAQ'):
                continue
            result_label.append(label)

        return result_label


    def process_label_return_single(slef, labels, company_id):
        workspace = company_id_dict.get(company_id, '222')

        # 标签修复
        labels = labels.replace('用户主动表示:用户主动表示:', '用户主动表示:')
        labels = labels.replace('在忙-在忙', '在忙')
        labels = labels.replace('活动介绍-', '活动介绍')
        labels = labels.replace('促成1-', '促成1')
        labels = labels.replace('信收1-', '信收1')
        labels = labels.replace('多次表示-', '')
        labels = labels.replace('在忙-不需要-会考虑', '不需要-会考虑')
        labels = labels.replace('在忙-不需要-无原因', '不需要-无原因')
        labels = labels.replace('打电话什么目的', '打电话有什么目的')
        labels = labels.replace(' 在忙-无原因', '在忙-无原因')
        labels = labels.replace('用户主动表示:二次不需要', '用户主动表示:不需要-无原因')
        labels = labels.replace('用户主动表示:三次不需要', '用户主动表示:不需要-无原因')
        labels = labels.replace('用户主动表示:三次在忙', '用户主动表示:在忙-可协商')
        labels = labels.replace('用户表示在忙必须挂机', '用户表示在忙-必须挂机')

        label_set = set()
        for label in labels.split(','):
            if ('坐席询问用户' in label or '坐席主动询问' in label or '坐席劝服用户' in label):
                label = label.replace('用户表示不需要', '不需要-无原因')
                label = label.replace('用户询问什么平台', '什么平台')
                label = label.replace('用户询问什么产品', '什么产品')
                label = label.replace('用户询问你是谁', '你是谁')
                label = label.replace('用户表示', '用户')

                label_set.add('-'.join(label.split('-')[1:]).replace('用户', ''))
            elif ('用户主动' in labels):
                label_set.add(label.split(':')[-1])
            else:
                label_set.add(label)

        return_label = {'意图': '', '态度': '不明态度', '实体': ''}
        for label in list(label_set):
            if label in ['肯定', '否定', '无明确回应']:
                return_label['态度'] = attitude_dict[label]
            elif '实体' in label:
                return_label['实体'] = label
            elif label in intent_name[workspace]:
                return_label['意图'] = label
                return_label['态度'] = attitude_dict[str(intent_name[workspace][label])]

        label_output = ''
        for label in return_label:
            label_output += '{}:{};'.format(label, return_label[label])

        return label_output


class checkDataNumHandler(tornado.web.RequestHandler):


    def get(self):
        min_date = self.get_argument('minDate')
        max_date = self.get_argument('maxDate')
        records = self.get_argument('records', "8000")
        company_id = self.get_argument('companyId', '')
        dm_version = self.get_argument('dmVersion', '')
        data_type = self.get_argument('dataType', 'ALL')
        human_pre = self.get_argument('humanPro', '')
        deal = self.get_argument('deal', '0')
        dialogue_round = self.get_argument('dialogueRound', '')
        robot_answer_duration = self.get_argument('robotAnswerDuration', '')
        human_answer_duration = self.get_argument('humanAnswerDuration', '10')

        data_num = self.task(min_date, max_date,
                             company_id, dm_version, records,
                             data_type, human_pre,
                             dialogue_round, robot_answer_duration, human_answer_duration, deal)

        self.write(str(data_num))


    def task(self, min_date, max_date,company_id, dm_version, records, data_type, human_pre,
             dialogue_round, robot_answer_duration, human_answer_duration, deal):
        results = mysql_connection(min_date, max_date, company_id, dm_version, records, data_type,
                                   dialogue_round, robot_answer_duration, human_answer_duration,
                                   deal)
        return len(results)


def get_online_prompt():
    results = hologres_connection(
        '2023-12-25 14:00:00', '2023-12-25 16:00:00',
        '2144', 'LLM版本')

    # url = 'http://172.26.2.56:8630/report/getDetailedRecord/?sessionId={}'
    url = 'http://8.142.85.77:8630/report/getDetailedRecord/?sessionId={}'
    result_json = list()
    train_data_prompt_dict = {
        'conversation': dict(),
        'question': dict(),
        'situation': dict(),
        'skill': dict(),
    }
    train_data_prompt_num_dict = {
        'conversation': 0,
        'question': 0,
        'situation': 0,
        'skill': 0,
    }
    for result in tqdm.tqdm(results):
        # print(result)
        session_id = result[2]

        response = requests.get(url.format(session_id)).text
        res = json.loads(response)['result'] if json.loads(response)['result'] else list()

        conversation_history = list()
        for j in range(len(res)):
            try:
                # 第一句的生成不是由大模型产生的
                if j == 0:
                    continue
                if res[j]['speakerType'] == 'IVR':
                    content = res[j]['msgContent']
                    content = content.split('#@')[-1].split('@@')[0]
                    conversation_history.append('销售员:'+content)

                    user_question = res[j]['llmResult']['intention'][0]['user_question']
                    sales_skill_name = res[j]['llmResult']['intention'][0]['sales_skill_name']
                    situation = res[j]['llmResult']['intention'][0]['user_situation']
                    skill_knowledge = res[j]['llmResult']['intention'][0]['skill_knowledge']
                    solution_knowledge = res[j]['llmResult']['intention'][0]['solution_knowledge']

                    result_json.append({
                        'question': user_question,
                        'situation': situation,
                        'skill': sales_skill_name,
                        'skill_knowledge': skill_knowledge,
                        'solution_knowledge': solution_knowledge,
                        'conversation': '\n'.join(conversation_history),
                    })

                    if user_question:
                        if user_question not in train_data_prompt_dict['question'].keys():
                            train_data_prompt_dict['question'][user_question] = 0
                        train_data_prompt_dict['question'][user_question] += 1

                        train_data_prompt_num_dict['question'] += 1

                    if situation:
                        # if situation not in train_data_prompt_dict['situation'].keys():
                        #     train_data_prompt_dict['situation'][situation] = 0
                        # train_data_prompt_dict['situation'][situation] += 1

                        train_data_prompt_num_dict['situation'] += 1

                    if sales_skill_name:
                        if sales_skill_name not in train_data_prompt_dict['skill'].keys():
                            train_data_prompt_dict['skill'][sales_skill_name] = 0
                        train_data_prompt_dict['skill'][sales_skill_name] += 1

                        train_data_prompt_num_dict['skill'] += 1

                    if content:
                        if content not in train_data_prompt_dict['conversation'].keys():
                            train_data_prompt_dict['conversation'][content] = 0
                        train_data_prompt_dict['conversation'][content] += 1

                        train_data_prompt_num_dict['conversation'] += 1


                    '''
                    question_prompt = user_questioning.QuestioningDisputeResolution('销售员', '\n'.join(conversation_history)).prompt
                    situation_prompt = user_situation.UserSituation('\n'.join(conversation_history)).prompt
                    skill_prompt = choose_sales_skill.SalesSkill('销售员', '\n'.join(conversation_history), '', '').prompt
                    conversation_prompt = conversation_agent.Conversation(
                        '销售员', '\n'.join(conversation_history), user_question, '', solution_knowledge,
                        sales_skill_name, skill_knowledge, situation
                    )

                    for prompt in [question_prompt, situation_prompt, skill_prompt, conversation_prompt]:
                        result_json.append({
                            'instruction': prompt,
                            'id': str(uuid.uuid1()),
                        })
                    '''

                elif res[j]['speakerType'] == 'USER':
                    content = res[j]['msgContent']
                    conversation_history.append('用户:'+content)
            except Exception as err:
                traceback.print_exc()

        # print('\n'.join(conversation_history))

    # utils.jdump(result_json, './online_prompt.json')

    for key in train_data_prompt_dict:
        train_data_prompt_dict[key] = dict(sorted(train_data_prompt_dict[key].items(), key=lambda item: item[1], reverse=True))


    print(train_data_prompt_dict['conversation'])
    print(train_data_prompt_dict['question'])
    print(train_data_prompt_dict['situation'])
    print(train_data_prompt_dict['skill'])

    print(train_data_prompt_num_dict)



def get_customer_all_dialogue():
    connection = psycopg2.connect(
        host="hgprecn-cn-2r42t31q3003-cn-zhangjiakou.hologres.aliyuncs.com",
        port=80,
        user="LTAI5tMsnFz7WRxKJyBDK5uv",
        password="SZxfgbrWpDYaHOR4HsU5ENxDOw6Xxt",
        dbname="screen"
    )

    cursor = connection.cursor()

    sql = '''
    with A as (
            select customer_id, dm_session_id, call_task_id, answer_duration, dm_version, call_start_time 
            from screen.public.outbound_call_result 
            where call_start_time > '2023-12-05 14:00:00' 
            and call_start_time < '2023-12-05 15:00:00' 
            and answer_time is not null 
            and dm_session_id is not null 
            and customer_id is not null 
            and company_id = 2144 
            and call_task_id = 1259),
         B as (
            select customer_id, dm_session_id, call_task_id, answer_duration, dm_version, call_start_time  
            from screen.public.outbound_call_result 
            where call_start_time > '2023-12-01 14:00:00' 
            and call_start_time < '2023-12-04 15:00:00' 
            and answer_time is not null 
            and dm_session_id is not null 
            and customer_id is not null 
            and company_id = 2144 
            and call_task_id = 523),
         C as (
            select customer_id, json_data 
            from public.outbound_call_case
            where company_id = 2144 
         )
    
    select A.customer_id, B.customer_id, C.customer_id, 
    B.dm_session_id, B.answer_duration, B.dm_version, B.call_start_time, C.json_data 
    from (A left join B on A.customer_id = B.customer_id) left join C on A.customer_id = C.customer_id  
    where A.customer_id = B.customer_id 
    and A.customer_id = C.customer_id 
    '''

    sql_info = '''
    select *
    from public.outbound_call_case
    where company_id = 2144
    and customer_id = 'K603791173761005373102'
    '''

    cursor.execute(sql)
    results = cursor.fetchall()

    url = 'http://8.142.85.77:8630/report/getDetailedRecord/?sessionId={}'

    wb = Workbook()
    ws = wb.active
    ws.append(['用户ID', '对话记录'])
    customer_id_dict = dict()
    for result in tqdm.tqdm(results):
        # print(result)
        name = ''
        customer_id = result[0]
        session_id = result[3]
        answer_duration = result[4]
        dm_version = result[5]
        call_start_time = result[6]
        json_data = result[7]
        if customer_id not in customer_id_dict:
            customer_id_dict[customer_id] = 0
        customer_id_dict[customer_id] += 1

        response = requests.get(url.format(session_id)).text
        res = json.loads(response)['result'] if json.loads(response)['result'] else list()
        conversation_history = list()
        for j in range(len(res)):
            try:
                # 第一句的生成不是由大模型产生的
                if res[j]['speakerType'] == 'IVR':
                    content = res[j]['msgContent']

                    if '先生' in content:
                        name = content.split('先生')[0][-1] + '先生'
                    elif '女士' in content:
                        name = content.split('女士')[0][-1] + '女士'

                    if dm_version == 'LLM版本':
                        content = content.split('#@')[-1].split('@@')[0]
                    else:
                        content_list = list()
                        for con in content.split('@#')[1:]:
                            content_list.append(con.split('#@')[0])
                        content = ''.join(content_list).split('||')[-1]
                    conversation_history.append('销售员:'+content)

                elif res[j]['speakerType'] == 'USER':
                    content = res[j]['msgContent'].split(']')[-1]
                    conversation_history.append('用户:'+content)
            except Exception as err:
                pass

        base_info = get_base_info(json.loads(json_data))
        base_info['用户姓名'] = name

        ws.append([
            customer_id, '\n'.join(conversation_history), str(base_info)
        ])

    wb.save('./历史对话记录.xlsx')

    for customer_id in customer_id_dict:
        if customer_id_dict[customer_id] > 1:
            print(customer_id)

    print(len(results))


def get_base_info(user_info):
    name = ''
    age = ''
    certificate = ''
    right = '无权益'
    rightCutRate = ''
    try:
        if not user_info:
            return {'用户姓名': name, '用户年龄': age, '初始用户权益': right, '券信息': certificate,
                    '降息幅度': rightCutRate}
        name = user_info.get('fullName', '')
        sex = user_info.get('sexByChineseWord', '')
        if name and sex:
            name = name + sex
        else:
            name = ''
        age = user_info.get('ageRange', '')
        certificate = ''
        low_interest = False
        coupon = False
        withdrawal = False

        couponDetailJson = user_info.get('couponDetailList', '')
        if couponDetailJson:
            couponDetailList = json.loads(couponDetailJson)
            for couponDetail in couponDetailList:
                activityName = couponDetail.get('activityName', '')
                if activityName:
                    if '免息' in activityName and '券' in activityName:
                        certificate = activityName
                        break
                    elif '券' in activityName:
                        certificate = activityName
                        continue

        cutRateTime = user_info.get('cutRateTime', '')
        try:
            if cutRateTime and datetime.strptime(cutRateTime, '%Y-%m-%d'):
                cutRateTime = datetime.strptime(cutRateTime, '%Y-%m-%d')
                current_date = datetime.now()
                days_left = (cutRateTime - current_date).days
                if days_left >= 0:
                    cutRate = user_info.get('cutRate', '')
                    if cutRate and cutRate == '是':
                        low_interest = True
                    # 降息前
                    beforeCutRate = user_info.get('beforeCutRate', '')
                    # 降息后
                    currentRate = user_info.get('currentRate', '')

                    beforeCutRate = beforeCutRate.replace('-', '')
                    currentRate = currentRate.replace('-', '')

                    if beforeCutRate and currentRate:
                        # 当前费率是这个 同一都是低
                        if '0.00015 ~ 0.00025' == currentRate:
                            rightCutRate = '利息降到最低'
                        # 当前费率
                        if '0.00025 ~ 0.00035' == currentRate and (
                                '0.00045 ~ 0.00055' == beforeCutRate or '0.00055 ~ 0.00065' == beforeCutRate):
                            rightCutRate = '利息降幅大'
                        if '0.00035 ~ 0.00045' == currentRate and '0.00055 ~ 0.00065' == beforeCutRate:
                            rightCutRate = '利息降幅大'

        except Exception:
            logging.error('日期转换错误')

        couponName = user_info.get('couponName', '')
        if couponName or certificate:
            coupon = True

        try:
            tradeTime = user_info.get('tradeTime', '')
            if tradeTime and datetime.strptime(tradeTime, '%Y-%m-%d'):
                withdrawal = True
        except Exception:
            logging.error('日期转换错误')
        rightList = list()
        if low_interest:
            rightList.append('已获得降息')
        if withdrawal:
            rightList.append('已获得提额')
        if coupon:
            rightList.append('已获得优惠券')
        if not rightList:
            rightList.append('无权益')
        right = ','.join(rightList)
        return {'用户姓名': name, '用户年龄': str(age), '初始用户权益': right, '券信息': certificate,
                '降息幅度': rightCutRate}
    except Exception:
        return {'用户姓名': name, '用户年龄': str(age), '初始用户权益': right, '券信息': certificate,
                '降息幅度': rightCutRate}



def NLU_online_data():

    min_date = '2024-01-25 00:00:00'
    max_date = '2024-01-25 23:00:00'
    company_id = 2132
    results = hologres_connection(min_date, max_date, company_id, '金条-拉新激活纯机-希爱-1212')

    url = 'http://172.26.2.56:8630/report/getDetailedRecord/?sessionId={}'
    # url = 'http://8.142.85.77:8630/report/getDetailedRecord/?sessionId={}'

    refuse_dict = {
        '1': 0, '2': 0, '3': 0, '4': 0, '5': 0
    }
    wb_w = Workbook()
    ws_w = wb_w.active
    ws_w.append([
        '角色', '文本语句', '态度', '意图'
    ])
    false_num = 0
    sentence_list = list()
    for result in tqdm.tqdm(results[:]):
        try:
            session_id = result[2]
            # print(session_id)
            response = requests.get(url.format(session_id)).text
            # print(json.loads(response))
            res = json.loads(response)['result']


            last_ivr_sentence = ''
            last_user_sentence = ''
            attitude = ''
            intent = ''
            sign = True
            user_response_list = list()
            for re in res:
                if re['speakerType'] == 'IVR':
                    sign = True
                    msg_content = re['msgContent']
                    last_ivr_sentence = msg_content


                    if user_response_list: # and user_response_list not in sentence_list:
                        # sentence_list.append(user_response_list)
                        for user_response in user_response_list:
                            ws_w.append([
                                '用户', user_response[0], user_response[1], user_response[2],
                            ])
                        ws_w.append([
                            '销售员', last_ivr_sentence, '', '',
                        ])
                    user_response_list = list()

                elif re['speakerType'] == 'USER':
                    sign = False
                    msg_content = re['msgContent']
                    NLU_result = re['idlResultJson']

                    refuse_num = 0
                    for refuse_word in [
                        "没有兴趣", "不感兴趣",
                        "不考虑", "不借钱", "不贷款", "没兴趣",
                        "不想用", '不想买', "用不上", "用不着",
                        "不可以", "不满意", "不方便", "不参加", "不同意",
                        "不需", "不要", "不用", "不行", "算了", "不好", "不加",
                        "不办", "不买", "不保", "不弄", "不做", "不整",
                    ]:
                        if refuse_word in msg_content:
                            refuse_num += msg_content.count(refuse_word)
                    if str(refuse_num) in refuse_dict:
                        refuse_dict[str(refuse_num)] += 1

                    if NLU_result:
                        NLU_result = json.loads(NLU_result)
                        last_user_sentence = NLU_result['query']
                        attitude = NLU_result['originalAttitude']
                        intent = NLU_result['standardQuery'] if NLU_result['standardQuery'] not in [
                            '肯定态度', '否定态度', '无态度', 'NOINTENT'] else ''
                    else:
                        last_user_sentence = msg_content
                        intent = ''
                        attitude = ''

                    if last_user_sentence not in sentence_list:
                        sentence_list.append(last_user_sentence)
                        user_response_list.append([last_user_sentence, attitude, intent])

            if not sign:
                ws_w.append([
                    '用户', last_user_sentence, attitude, intent,
                ])


        except Exception as err:
            print(session_id)
            false_num += 1

    print(false_num)
    print(refuse_dict)
    # wb_w.save('./{}_{}.xlsx'.format(company_id, 'QYZD-23年带操作版-1219'))


if __name__ == '__main__':


    '''
    tornado.options.parse_command_line()
    app = tornado.web.Application(handlers=[
        (r"/dialogue_data", dialogueDataHandler),
        (r"/check_data_num", checkDataNumHandler),
    ], autoreload=False, debug=False)
    http_server = tornado.httpserver.HTTPServer(app)
    http_server.bind(8100)
    http_server.start(1)
    tornado.ioloop.IOLoop.current().start()
    '''

    # get_customer_all_dialogue()

    # get_online_prompt()

    NLU_online_data()


    print('weeqwe'.count('w'))



